# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Column
from sqlalchemy import (
    Integer, String,
    BLOB, Text, DATETIME)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

__all__ = [
    'User', 'WebSessionStore', 'UserDetail',
    'Recipe', 'Dish', 'Headline', 'Category',
    'TrendCategory', 'session']

Base = declarative_base()


class WebSessionStore(Base):
    ''' store session info of users'''
    __tablename__ = 'sessions'
    id = Column(String(50), primary_key=True)
    value = Column(BLOB)


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    email = Column(String(100), index=True)
    password = Column(String(100))
    nickname = Column(String(100))
    ctime = Column(DATETIME, index=True)

    @classmethod
    def valid(cls, uid, passwd):
        user = session.query(User).filter(User.nickname == uid).first()
        if user and user.password == passwd:
            return True
        return False

    @classmethod
    def register(cls, email, password, nickname):
        # unique email
        if session.query(User).filter(User.email == email).first():
            return (False, 'email exists')
        if session.query(User).filter(User.nickname == nickname).first():
            return (False, 'nickname exists')
        u = User(email=email, password=password, nickname=nickname)
        session.add(u)
        session.flush()
        return (True, '')


class UserDetail(Base):
    __tablename__ = 'user_detail'

    id = Column(Integer, primary_key=True)
    intro = Column(String(1024))
    gender = Column(String(5))
    birthday = Column(String(12))
    hometown = Column(String(255))
    city = Column(String(255))
    job = Column(String(255))
    pic_url = Column(String(255))


class Recipe(Base):
    __tablename__ = 'recipe'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, index=True)
    pic_url = Column(String(255))
    title = Column(String(255))
    intro = Column(Text)
    material = Column(String(1024))
    detail = Column(Text)
    tips = Column(Text)
    ctime = Column(DATETIME)
    dish_counter = Column(Integer, index=True)

    @classmethod
    def get_recent(cls, limit):
        return session.query(Recipe).order_by(Recipe.ctime.desc())\
            .limit(limit).all()

    @classmethod
    def get_trend(cls, limit):
        return session.query(Recipe).order_by(Recipe.dish_counter.desc())\
            .limit(limit).all()

    @classmethod
    def get_by_user(cls, user_id, limit):
        return session.query(Recipe)\
            .filter(Recipe.user_id == user_id)\
            .limit(limit).all()


class Dish(Base):
    __tablename__ = 'dish'

    id = Column(Integer, primary_key=True)
    recipe_id = Column(Integer, index=True)
    user_id = Column(Integer, index=True)
    pic_url = Column(String(255))
    intro = Column(Text)
    ctime = Column(DATETIME)


class Headline(Base):
    __tablename__ = 'headline'

    id = Column(Integer, primary_key=True)
    recipe_id = Column(Integer, index=True)
    rank = Column(Integer, index=True)

    @classmethod
    def get_headline(cls, limit):
        ids = session.query(Headline.recipe_id).order_by(Headline.rank)\
            .limit(limit).all()
        ids = [i[0] for i in ids]
        if not ids:
            return []

        recipes = session.query(Recipe).filter(Recipe.id.in_(ids)).all()

        d = {}
        for r in recipes:
            d[r.id] = r
        result = []
        for i in ids:
            result.append(d[i])
        return result


class Category(Base):
    __tablename__ = 'category'

    id = Column(Integer, primary_key=True)
    title = Column(String(255))
    intro = Column(Text)


class TrendCategory(Base):
    __tablename__ = 'hot_material'

    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, index=True)
    rank = Column(Integer, index=True)


DB_CONN = 'mysql://xcf:xcf@localhost/xcf?charset=utf8'
engine = create_engine(
    DB_CONN, pool_recycle=3600, pool_size=50, max_overflow=100)

#Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
